unit main;

{$mode objfpc}{$H+}

interface

uses
  db, BufDataset, eventlog, Forms, variants, SysUtils, StdCtrls, ExtCtrls,
  Dialogs, Controls, fpspreadsheet, Graphics, Buttons, ActnList, DbCtrls,
  ZConnection, ZDataset, Classes, types, Grids, PopupNotifier, LR_Class,
  LR_DBSet, LR_Desgn, LR_E_HTM, LR_E_TXT, lr_e_pdf;

type

  { TFrmMain }

  TFrmMain = class(TForm)
    actCounterparties: TAction;
    actCashRegister: TAction;
    actComingInvoicesList: TAction;
    actHistory: TAction;
    actSettings: TAction;
    actReturnGoodsFromClientList: TAction;
    actNewReturnGoodsFromClient: TAction;
    actOutPutInvoicesList: TAction;
    actNewOutPutInvoice: TAction;
    actReturnGoodsToSupplierList: TAction;
    actNewReturnGoodsToSupplier: TAction;
    actNewComingInvoice: TAction;
    actSite: TAction;
    actPricesList: TAction;
    actReports: TAction;
    actNomenclatureCreateEditGroup: TAction;
    actSupply: TAction;
    actRepair: TAction;
    actTrade: TAction;
    actNomenclature: TAction;
    actStoreHouses: TAction;
    ActionList: TActionList;
    ApplicationProperties: TApplicationProperties;
    btnSettings: TBitBtn;
    btnHistory: TBitBtn;
    btnStoreHouses: TBitBtn;
    btnCashRegister: TBitBtn;
    BitBtn11: TBitBtn;
    btnNomenclature: TBitBtn;
    btnTrades: TBitBtn;
    btnRepairs: TBitBtn;
    btnSupply: TBitBtn;
    btnCounterParties: TBitBtn;
    btnReports: TBitBtn;
    btnPriceList: TBitBtn;
    btnSite: TBitBtn;
    Datasource: TDatasource;
    DSHistory: TDatasource;
    DSOutPutGoodsView: TDatasource;
    DSGroupSelect: TDatasource;
    DSOutPutInvoices: TDatasource;
    DSStoreHousesNomenclatureView: TDatasource;
    DSComingInvoices: TDatasource;
    DSComingGoodsView: TDatasource;
    DSCounterPartiesGroup: TDatasource;
    DSCounterParties: TDatasource;
    DSQuery: TDatasource;
    DSNomenclature: TDatasource;
    DSStoreHouses: TDatasource;
    EventLog: TEventLog;
    frDBDataSet: TfrDBDataSet;
    frDesigner: TfrDesigner;
    frHTMExport: TfrHTMExport;
    frReport: TfrReport;
    frTextExport: TfrTextExport;
    frTNPDFExport: TfrTNPDFExport;
    ImageList: TImageList;
    ImageListButtons: TImageList;
    ImageList2: TImageList;
    PopupNotifier: TPopupNotifier;
    SaveDialog: TSaveDialog;
    TrayIcon: TTrayIcon;
    ZConnection: TZConnection;
    ZQComingInvoicesinvoiceid: TStringField;
    ZQOutPutInvoices: TZQuery;
    ZQComingGoodsViewid: TLongintField;
    ZQComingGoodsViewinvoiceid: TLongintField;
    ZQComingGoodsViewnomenclatureid: TLongintField;
    ZQComingGoodsViewprice: TFloatField;
    ZQComingGoodsViewquantity: TLongintField;
    ZQComingGoodsViewtitle: TStringField;
    ZQComingInvoicescounterpartyid: TLongintField;
    ZQComingInvoicescounterpartytitle: TStringField;
    ZQComingInvoicesdate: TDateTimeField;
    ZQComingInvoicesid: TLongintField;
    ZQComingInvoicesstorehouseid: TLongintField;
    ZQComingInvoicesstorehousetitle: TStringField;
    ZQOutPutInvoicescounterpartyid: TLongintField;
    ZQOutPutInvoicescounterpartytitle: TStringField;
    ZQOutPutInvoicesdate: TDateTimeField;
    ZQOutPutInvoicesid: TLongintField;
    ZQOutPutInvoicesstorehouseid: TLongintField;
    ZQOutPutInvoicesstorehousetitle: TStringField;
    ZQuery: TZQuery;
    ZQComingInvoices: TZQuery;
    ZQComingGoodsView: TZQuery;
    DBQuery: TZQuery;
    ZQStoreHousesNomenclatureView: TZQuery;
    ZQGroupSelect: TZQuery;
    ZQOutPutGoodsView: TZQuery;
    ZTHistory: TZTable;
    ZTCounterPartiesGroup2: TZTable;
    ZTCounterParties: TZTable;
    ZTCounterPartiesgroup: TBooleanField;
    ZTCounterPartiesid: TLongintField;
    ZTCounterPartiesinfo: TMemoField;
    ZTCounterPartiesparent: TLongintField;
    ZTCounterPartiestitle: TStringField;
    ZTHistorydate: TDateTimeField;
    ZTHistoryid: TLongintField;
    ZTHistoryinfo: TMemoField;
    ZTHistorytitle: TMemoField;
    ZTNomenclaturearticleinside: TStringField;
    ZTNomenclaturearticleofgoods: TStringField;
    ZTNomenclature: TZTable;
    ZTNomenclaturegroup: TBooleanField;
    ZTNomenclatureid: TLongintField;
    ZTNomenclatureparentid: TLongintField;
    ZTNomenclatureprice: TFloatField;
    ZTNomenclaturepricebigwholesale: TLongintField;
    ZTNomenclaturepriceretail: TLongintField;
    ZTNomenclaturepricesmallwholesale: TLongintField;
    ZTNomenclaturequantity: TLongintField;
    ZTNomenclaturequantitymin: TLongintField;
    ZTNomenclaturetitle: TStringField;
    ZTStoreHouses: TZTable;
    ZTStoreHousesid: TLongintField;
    ZTStoreHousestitle: TStringField;
    procedure actComingInvoicesListExecute(Sender: TObject);
    procedure actCounterpartiesExecute(Sender: TObject);
    procedure actHistoryExecute(Sender: TObject);
    procedure actNewComingInvoiceExecute(Sender: TObject);
    procedure actNewOutPutInvoiceExecute(Sender: TObject);
    procedure actNewReturnGoodsToSupplierExecute(Sender: TObject);
    procedure actNomenclatureExecute(Sender: TObject);
    procedure actOutPutInvoicesListExecute(Sender: TObject);
    procedure actPricesListExecute(Sender: TObject);
    procedure actSettingsExecute(Sender: TObject);
    procedure actSupplyExecute(Sender: TObject);
    procedure actReportsExecute(Sender: TObject);
    procedure actStoreHousesExecute(Sender: TObject);
    procedure actTradeExecute(Sender: TObject);
    procedure ApplicationPropertiesException(Sender: TObject; E: Exception);
    procedure BitBtn11Click(Sender: TObject);
    procedure FormCloseQuery(Sender: TObject; var CanClose: boolean);
    procedure FormCreate(Sender: TObject);
    procedure clearSelect;
    procedure history(const title,info:String);
    procedure frReportGetValue(const ParName: String; var ParValue: Variant);
    function lastInsertID(const TableName: String): Integer;
    function tableQuantity(const tableName,where: String): Integer;
    function getIsHaveRecord(const storehouseid,nomenclatureid: String):Boolean;
    function getIsTaHaveRecord(const table,where: String):Boolean;
    procedure updateStoreHousesGoodsCount(quantity:String;const storehouseid,nomenclatureid: String);
    procedure KeyVerifyInt(Sender: TObject; var Key: Char);
    procedure KeyVerifyFloat(Sender: TObject; var Key: Char);
    procedure FillCell(StringGrid : TStringGrid; Col, Row : Integer; BkCol, TextCol : TColor);
    function rData(myWorksheet: TsWorksheet;parentID:String):Boolean;
  private
    { private declarations }
  public
    { public declarations }
    program_dir:String;
    nomenclatureSelectedId:Integer;
    nomenclatureSelectedTitle:String;
    counterPartieSelectedId:Integer;
    counterPartieSelectedTitle:String;
    storeHouseSelectedId:Integer;
    storeHouseSelectedTitle:String;
    ///////////////////////////////
    ExchangeRate: Double; // курс валют
    MarkUp: Integer; // націнка за запчастини
    entrepreneur:String; //Підприємець
    purveyor:String; //Постачальник
    recipient:String; //Одержувач
    invoiceID:String;
    comingInvoiceID:String;
    debugMode:Boolean;
    recordN:Integer;
  end;

var
  FrmMain: TFrmMain;

implementation

{$R *.lfm}

{ TFrmMain }

uses LCLIntf, history, LCLType,tests, pricelist, settings, supply,trade, storehouses,nomenclature,returngoodstosupplierelement,counterparties,cominginvoiceslist,outputinvoiceslist,cominginvoiceelement,outputinvoiceselement,reportslist;

procedure TFrmMain.updateStoreHousesGoodsCount(quantity:String;const storehouseid,nomenclatureid: String);
begin
  if not getIsHaveRecord(storehouseid,nomenclatureid) then begin
    FrmMain.ZQuery.SQL.Text:='INSERT INTO nomenclatureonstorehouses VALUES(NULL,'+storehouseid+','+nomenclatureid+','+quantity+')';
    FrmMain.ZQuery.ExecSQL;
  end else begin
    FrmMain.ZQuery.SQL.Text:='UPDATE nomenclatureonstorehouses SET quantity=quantity+'+quantity+' WHERE storehouseid='+storehouseid+' AND nomenclatureid='+nomenclatureid;
    FrmMain.ZQuery.ExecSQL;
  end;
  FrmMain.ZQuery.SQL.Text:='UPDATE nomenclature SET quantity=quantity+'+quantity+' WHERE id='+nomenclatureid;
  FrmMain.ZQuery.ExecSQL;
end;

procedure TFrmMain.history(const title,info:String);
var
 query: TZQuery;
begin
  try
    try
     query := TZQuery.Create(nil);
     query.Connection := ZConnection;
    with query do begin
      SQL.Text := 'INSERT INTO history VALUES(NULL,CURRENT_TIMESTAMP,'''+title+''','''+info+''')';
      ExecSQL;
    end;
    finally
     query.Free ;
     query:= nil;
    end;
  except
    on e : Exception do begin
      EventLog.Error('history '+e.Message);
    end;
  end;
end;

function TFrmMain.getIsHaveRecord(const storehouseid,nomenclatureid: String):Boolean;
var
  res:Boolean;
begin
  FrmMain.ZQuery.SQL.Text:='SELECT id FROM nomenclatureonstorehouses WHERE storehouseid='+storehouseid+' AND nomenclatureid='+nomenclatureid;
  FrmMain.DSQuery.DataSet.Open;
  res:=True;
  if FrmMain.DSQuery.DataSet.RecordCount=0 then begin
    res:=False;
  end;
  FrmMain.DSQuery.DataSet.Close;
  Result:=res;
end;

function TFrmMain.getIsTaHaveRecord(const table,where: String):Boolean;
var
  res:Boolean;
begin
  DBQuery.SQL.Text:='SELECT id FROM '+table+' WHERE '+where;
  DBQuery.Open;
  res:=True;
  if DBQuery.RecordCount=0 then begin
    res:=False;
  end;
  DBQuery.Close;
  Result:=res;
end;

function TFrmMain.rData(myWorksheet: TsWorksheet;parentID:String):Boolean;
var
  myCell: PCell;
  res:Boolean;
  tmpQuery: TZQuery;
  i:Integer;
  id:String;
begin
  res:=False;
  try
   tmpQuery := TZQuery.Create(nil);
   tmpQuery.Connection:=ZConnection;
   tmpQuery.SQL.Text:='SELECT id,"group",parentid,title,articleofgoods,articleinside,price,priceretail,pricesmallwholesale,pricebigwholesale,'+
   'quantity FROM nomenclature WHERE parentid='+parentID+' ORDER BY title,price';
   tmpQuery.Active:=True;
   tmpQuery.Open;
   if tmpQuery.RecordCount>0 then begin
     tmpQuery.First;
     for i:=1 to tmpQuery.RecordCount do begin
       if (tmpQuery.FieldByName('group').AsBoolean) AND getIsTaHaveRecord('nomenclature','parentid='+parentID) then begin
         myWorksheet.WriteUTF8Text(recordN,0,tmpQuery.FieldByName('title').AsString);
         myCell:= myWorksheet.GetCell(recordN,0);
         myCell^.BackgroundColor:=scCYAN;
         myCell^.UsedFormattingFields:=[uffBold,uffBackgroundColor];
         recordN:=recordN+1;
       end else begin
         if (NOT tmpQuery.FieldByName('group').AsBoolean) then begin
           myWorksheet.WriteUTF8Text(recordN,0,tmpQuery.FieldByName('title').AsString);
           myWorksheet.WriteNumber(recordN,1,tmpQuery.FieldByName('price').AsCurrency*(((tmpQuery.FieldByName('priceretail').AsInteger*0.01)+1)*FrmMain.ExchangeRate));
           myWorksheet.WriteNumber(recordN,2,tmpQuery.FieldByName('price').AsCurrency*(((tmpQuery.FieldByName('pricesmallwholesale').AsInteger*0.01)+1)*FrmMain.ExchangeRate));
           myWorksheet.WriteNumber(recordN,3,tmpQuery.FieldByName('price').AsCurrency*(((tmpQuery.FieldByName('pricebigwholesale').AsInteger*0.01)+1)*FrmMain.ExchangeRate));
           myCell:= myWorksheet.GetCell(recordN,0);
           myCell^.UsedFormattingFields:=[uffBold];
           recordN:=recordN+1;
         end;
       end;
       if (tmpQuery.FieldByName('group').AsBoolean) AND getIsTaHaveRecord('nomenclature','parentid='+tmpQuery.FieldByName('id').AsString) then begin
         id:=tmpQuery.FieldByName('id').AsString;
         rData(myWorksheet,id);
         res:=True;
       end;
       tmpQuery.Next;
       frmPriceList.ProgressBar.Position:=recordN;
     end;
   end;
  finally
   tmpQuery.Free ;
   tmpQuery:= nil;
   Result:=res;
  end;
end;

function TFrmMain.lastInsertID(const TableName: String): Integer;
var
 query: TZQuery;
 res:Integer;
begin
  try
   res:=0;
   query := TZQuery.Create(nil);
   query.Connection:=ZConnection;
  with query do begin
    SQL.Text := 'SELECT MAX(id) AS last_insert_id FROM :TableName';
    Params.ParamByName('TableName').AsString := TableName;
    Open;
    res:=FieldByName('last_insert_id').AsInteger;
  end;
  finally
   query.Free ;
   query:= nil;
   Result:=res;
  end;
end;

function TFrmMain.tableQuantity(const tableName,where: String): Integer;
var
 query: TZQuery;
 res: Integer;
begin
  try
    try
     res := 0;
     query := TZQuery.Create(nil);
     query.Connection := ZConnection;
    with query do begin
      SQL.Text := 'SELECT quantity FROM '+tableName+' WHERE '+where;
      //ShowMessage(SQL.Text);
      Active:=True;
      Open;
      if RecordCount >= 1 then begin
        res := FieldByName('quantity').AsInteger;
      end else begin
        EventLog.Info('quantity<=0');
      end;
    end;
    finally
     query.Free ;
     query:= nil;
     Result:=res;
    end;
  except
    on e : Exception do begin
      EventLog.Error('tableQuantity '+e.Message);
    end;
  end;
end;

procedure TFrmMain.FillCell(StringGrid : TStringGrid; Col, Row : Integer;BkCol, TextCol : TColor);
var
  Rect : TRect;
  TempPString:Array [0..255] of char;
begin
  Rect := StringGrid.CellRect(Col, Row);
  with StringGrid.Canvas do begin
    Brush.Color := BkCol;
    FillRect(Rect);
    StrPCopy(TempPString,StringGrid.Cells[Col,Row]);
    Font.Color := TextCol;
    //DrawText(StringGrid.Canvas.Handle,TempPString,-1,Rect,DT_RIGHT);
    TextOut(Rect.Left + 2, Rect.Top + 2, StringGrid.Cells[Col, Row]);
  end;
end;

procedure TFrmMain.KeyVerifyInt(Sender: TObject; var Key: Char);
begin
  // #8 = BackSpace
  if Not(Key in ['0'..'9',#8]) then
  begin
    Key := #0;
    Beep;
    exit;
  end;
  if Sender.ClassName = 'TStringGrid' then begin
    if (Not(Key in ['0'..'9',#8])) AND (Length(TStringGrid(Sender).Cells[TStringGrid(Sender).Col,TStringGrid(Sender).Row])>8) then begin
      Key := #0;
      Beep;
      exit;
    end;
  end;
  if Sender.ClassName = 'TEdit' then begin
    if (Key <> #8) AND (Length(TEdit(Sender).Text)>8) then begin
      Key := #0;
      Beep;
      exit;
    end;
  end;
  if Sender.ClassName = 'TLabeledEdit' then begin
    if (Key <> #8) AND (Length(TLabeledEdit(Sender).Text)>8) then begin
      Key := #0;
      Beep;
      exit;
    end;
  end;
end;

procedure TFrmMain.KeyVerifyFloat(Sender: TObject; var Key: Char);
begin
  if Not(Key in ['0' .. '9', ',', '.', #8]) then
  begin
    Key := #0;
    Beep;
    exit;
  end;
  if Sender.ClassName = 'TStringGrid' then begin
    if (Key in [',', '.']) AND (Pos('.',TStringGrid(Sender).Cells[TStringGrid(Sender).Col,TStringGrid(Sender).Row])>0)  then begin
      Key := #0;
      Beep;
      exit;
    end;
  end;
  if Sender.ClassName = 'TEdit' then begin
    if (Key in [',', '.']) AND (Pos('.',TEdit(Sender).Text)>0)  then begin
      Key := #0;
      Beep;
      exit;
    end;
  end;
  if Sender.ClassName = 'TLabeledEdit' then begin
    if (Key in [',', '.']) AND (Pos('.',TLabeledEdit(Sender).Text)>0) then begin
      Key := #0;
      Beep;
      exit;
    end;
  end;
  if Key in [',', '.'] then begin
    Key := '.';
    exit;
  end;
end;

procedure TFrmMain.clearSelect;
begin
  nomenclatureSelectedId := 0;
  nomenclatureSelectedTitle := '';
end;

procedure TFrmMain.frReportGetValue(const ParName: String; var ParValue: Variant
  );
begin
  if ParName = 'COMINGINVOICEID' then begin
    ParValue := comingInvoiceID;
  end;
  if ParName = 'PURVEYOR' then begin
    ParValue := purveyor;
  end;
  if ParName = 'ENTREPRENEUR' then begin
    ParValue := entrepreneur;
  end;
  if ParName = 'RECIPIENT' then begin
    ParValue := recipient;
  end;
end;

procedure TFrmMain.actStoreHousesExecute(Sender: TObject);
begin
  frmStoreHouses.ShowModal();
end;

procedure TFrmMain.actTradeExecute(Sender: TObject);
begin
  frmTrade.ShowModal;
end;

procedure TFrmMain.ApplicationPropertiesException(Sender: TObject;
  E: Exception);
begin
  EventLog.Error(E.Message);
end;

procedure TFrmMain.BitBtn11Click(Sender: TObject);
begin
  frmTests.ShowModal;
end;

procedure TFrmMain.FormCloseQuery(Sender: TObject; var CanClose: boolean);
begin
  EventLog.Info('Exit');
end;

procedure TFrmMain.actNomenclatureExecute(Sender: TObject);
begin
  frmNomenclature.ShowModal;
end;

procedure TFrmMain.actOutPutInvoicesListExecute(Sender: TObject);
begin
  frmOutPutInvoicesList.ShowModal;
end;

procedure TFrmMain.actPricesListExecute(Sender: TObject);
begin
  frmPriceList.ShowModal;
end;

procedure TFrmMain.actSettingsExecute(Sender: TObject);
begin
  frmSettings.lbledtExchangeRate.Text:= FloatToStr(ExchangeRate);
  frmSettings.memName.Lines.Text := entrepreneur;
  frmSettings.ShowModal;
end;

procedure TFrmMain.actSupplyExecute(Sender: TObject);
begin
  frmSupply.ShowModal;
end;

procedure TFrmMain.actReportsExecute(Sender: TObject);
begin
  frmReportsList.ShowModal;
end;

procedure TFrmMain.actCounterpartiesExecute(Sender: TObject);
begin
  frmCounterParties.ShowModal;
end;

procedure TFrmMain.actHistoryExecute(Sender: TObject);
begin
  frmHistory.ShowModal;
end;

procedure TFrmMain.actNewComingInvoiceExecute(Sender: TObject);
begin
  frmComingInvoiceElement.invoiceIsNew := True;
  FrmMain.history('Прихідна накладна','спроба створення п/н');
  frmComingInvoiceElement.ShowModal;
end;

procedure TFrmMain.actNewOutPutInvoiceExecute(Sender: TObject);
begin
  frmOutputInvoicesElement.invoiceIsNew := True;
  FrmMain.history('Вихідна накладна','спроба створення в/н');
  frmOutputInvoicesElement.ShowModal;
end;

procedure TFrmMain.actNewReturnGoodsToSupplierExecute(Sender: TObject);
begin
  frmReturnGoodsToSupplierElement.ShowModal;
end;

procedure TFrmMain.actComingInvoicesListExecute(Sender: TObject);
begin
  frmComingInvoicesList.ShowModal;
end;

procedure TFrmMain.FormCreate(Sender: TObject);
var
  i:Integer;
begin
  {$ifdef windows}
    program_dir := ExtractFilePath(Application.ExeName);
  {$endif}
  {$ifdef LINUX}
    program_dir := '';
  {$endif}
  debugMode := True;
  EventLog.FileName := program_dir+'log.txt';
  EventLog.Active := True;
  EventLog.Info('Started');
  ZConnection.Database := program_dir+'db.db';
  //ShowMessage(ZConnection.Database);
  ZConnection.Connected := True;
  if (ZConnection.Connected) then begin
    //Loading Settings
    DBQuery.SQL.Text := 'SELECT * FROM settings ORDER BY id';
    DBQuery.Open;
    if (DBQuery.RecordCount = 3) then begin
      for i := 1 to DBQuery.RecordCount do begin
        DBQuery.RecNo := i;
        if (i = 2) then begin
           ExchangeRate := StrToFloat(DBQuery.FieldByName('value').AsString);
        end;
        if (i = 3) then begin
           entrepreneur := DBQuery.FieldByName('value').AsString;
        end;
      end;
    end else begin
      EventLog.Error('Settings not have element');
    end;
    DBQuery.Active := False;
    //End Loading Settings
    //Activate Tables and Queries
    ZTStoreHouses.Active := True;
    ZTNomenclature.Active := True;
    ZTCounterPartiesGroup2.Active := True;
    ZTCounterParties.Active := True;
    ZQComingInvoices.Active := True;
    ZQOutPutInvoices.Active := True;
    ZQStoreHousesNomenclatureView.Active := True;
    ZQComingGoodsView.Active := True;
    ZQOutPutGoodsView.Active := True;
    ZTHistory.Active := True;
    //End activate Tables and Queries
  end else begin
     ShowMessage('Помилка з''єднання з базою данних');
  end;
end;

end.

